// 封装数据库，链接MYSQL
// 允许我们在node.js上链接我们本地的MYSQL
const mysql = require('mysql2/promise');
const config = require('../config/index.js');

// 创建新的线程连接mysql,查看文档，创造线程池
const pool = mysql.createPool({
    // config文件夹下database对象中的各个属性
    database: config.database.DATABASE,
    user: config.database.USERNAME,
    password: config.database.PASSWORD,
    port: config.database.PORT,
    host: config.database.HOST
});

// 查询数据库中所有数据
const allServices = {
    async Query(sql, values) {
        try {
            // 通过线程池pool的getConnection方法，将pool的值给到conn
            const conn = await pool.getConnection();
            // 对连接执行某些操作,rows就是sql
            const [rows, fields] = await conn.query(sql, values)
            // 释放连接
            pool.releaseConnection(conn);
            // 返回查询结果
            return Promise.resolve(rows);
        } catch (err) {
            // 抓捕错误
            return Promise.reject(err);
        }
    }
}

// 登录
const userLogin = (username, password) => {
    let _sql = `select * from users where username="${username}" and password="${password}";`;
    return allServices.Query(_sql)
}

// 查账账号
const userFind = (username) => {
    let _sql = `select * from users where username="${username}";`
    return allServices.Query(_sql)
}

// 检测注册页面用户的输入，如果数据库中没有数据存储则插入
const userRegister = (username, password, nickname) => {
    let _sql = `INSERT INTO users(username,password,nickname) VALUES("${username}","${password}","${nickname}");`
    return allServices.Query(_sql)
}

// 根据分类查找笔记
const findNoteListByType = (type, id) => {
    let _sql = `SELECT * FROM note WHERE note_type="${type}" and userId="${id}";`

    return allServices.Query(_sql)
}

const findNoteDetailById = (id) => {
    let _sql = `select * from note where id="${id}"`;
    return allServices.Query(_sql);
}

const publishNote = (obj) => {
    const date = new Date()
    const m_time = `${date.getFullYear()}年${date.getMonth() + 1}月${date.getDate()}日`
    let _sql = `insert into note (userid,title,note_type,note_content,c_time,m_time,head_img,nickname) values
     ("${obj.user_id}","${obj.title}","${obj.note_type}","${obj.note_content}","${obj.c_time}","${m_time}","${obj.head_img}","${obj.nickname}");`
    return allServices.Query(_sql);
}


module.exports = {
    userLogin,
    userFind,
    userRegister,
    findNoteListByType,
    findNoteDetailById,
    publishNote
}
